﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MSSQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.SQLserver
{
    public class Industry : IIndustry
    {
/*
                private SqlConnection _internalConnection;
                private SqlTransaction _internalADOTransaction = null;
        
                public void BeginADOTransaction()
                {
                    if (_internalConnection.State != System.Data.ConnectionState.Open)
                        _internalConnection.Open();
                    _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                public void RollBackTransaction()
                {
                    _internalADOTransaction.Rollback();
                    _internalADOTransaction = null;
                }
                public void CommitADOTransaction()
                {
                    _internalADOTransaction.Commit();
                    _internalADOTransaction = null;
                }
                public void Open(string connString)
                {
                    if (_internalConnection == null)
                        _internalConnection = new SqlConnection(connString);
                    if (_internalConnection.State != ConnectionState.Open)
                        _internalConnection.Open();
                }
                public void Close()
                {
                    if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                        _internalConnection.Close();
                }
        
                //SQL string
                private const string SQL_INDUSTRY_INSERT = "INSERT INTO INDUSTRY(NAME, CODE, SYSTEMID, BELONGTO, DEFINITION, LANG, _LEVEL) VALUES (@NAME, @CODE, @CLASSIFICATION, @BELONGTO, @DEFINITION, @LANG, @LEVEL); SELECT ID=@@IDENTITY;";
                private const string SQL_INDUSTRY_DELETE = "DELETE FROM INDUSTRY WHERE ID=@ID";
                private const string SQL_INDUSTRY_UPDATE = "UPDATE INDUSTRY SET NAME=@NAME, CODE=CODE, DEFINITION=@DEFINITION WHERE ID=@ID";
        
                private const string SQL_INDUSTRY_GETBYID = "SELECT * FROM V_INDUSTRY WHERE ID=@ID";
                private const string SQL_INDUSTRY_GETBYLANG = "SELECT * FROM V_INDUSTRY WHERE LANG=@LANG";
                private const string SQL_INDUSTRY_GETBY_CLASSANDLANG = "SELECT * FROM V_INDUSTRY WHERE LANG=@LANG AND CLASSIFICATION=@CLASSIFICATION";
                private const string SQL_INDUSTRY_GETALL = "SELECT * FROM V_INDUSTRY";
                private const string SQL_INDUSTRY_TOPLEVELINCLASS = "SELECT * FROM V_INDUSTRY WHERE BELONGTO=0 AND CLASSIFICATION=@CLASSIFICATION";
                private const string SQL_INDUSTRY_TOPLEVEL_BYLANGANDCLASS = "SELECT * FROM V_INDUSTRY WHERE BELONGTO=0 AND CLASSIFICATION=@CLASSIFICATION AND LANG=@LANG";
                private const string SQL_INDUSTRY_GETBY_IDANDLEVEL = "SELECT * FROM V_INDUSTRY WHERE LANG=@LANG AND _LEVEL=@LEVEL";
                private const string SQL_INDUSTRY_GETBY_LANGANDCODE = "SELECT * FROM V_INDUSTRY WHERE LANG=@LANG AND CODE LIKE '%'+@CODE+'%'";
                private const string SQL_INDUSTRY_GETBY_LANGANDCLASS = "SELECT * FROM V_INDUSTRY WHERE LANG=@LANG AND CLASSIFICATION=@CLASSIFICATION";
        
                private const string SQL_INDUSTRY_GETIDBYCODE = "SELECT ID FROM INDUSTRY WHERE CODE=@CODE";
                private const string SQL_INDUSTRY_GETCHILDREN = "SELECT * FROM V_INDUSTRY WHERE BELONGTO=@ID";
                private const string SQL_INDUSTRY_GETMAXLEVEL = "SELECT MAX(_LEVEL) AS MAXLEVEL FROM V_INDUSTRY";
        
                #region Parameters
                private const string PARM_ID = "@ID";
                private const string PARM_NAME = "@NAME";
                private const string PARM_CODE = "@CODE";
                private const string PARM_CLASSIFICATION = "@CLASSIFICATION";
                private const string PARM_BELONGTO = "@BELONGTO";
                private const string PARM_DEFINITION = "@DEFINITION";
                private const string PARM_LANG = "@LANG";
                private const string PARM_LEVEL = "@LEVEL";
                #endregion
        
                //Functions
        
        
                #region IIndustry Members
        
                public int InsertIndustry(IndustryData item)
                {
                    try
                    {
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_NAME, SqlDbType.NVarChar, 500),
                            new SqlParameter(PARM_CODE, SqlDbType.NVarChar, 50),
                            new SqlParameter(PARM_CLASSIFICATION, SqlDbType.Int),
                            new SqlParameter(PARM_BELONGTO, SqlDbType.Int),
                            new SqlParameter(PARM_DEFINITION, SqlDbType.NText),
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_LEVEL, SqlDbType.Int)};
                        parms[0].Value = item.Name;
                        parms[1].Value = item.Code;
                        parms[2].Value = item.Classification;
                        parms[3].Value = item.BelongTo;
                        parms[4].Value = item.Definition;
                        parms[5].Value = item.LangId;
                        parms[6].Value = item.Level;
                        return SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_INSERT, parms);
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public void DeleteIndustry(int id)
                {
                    try
                    {
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = id;
                        SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_DELETE, parm1);
                    }
                    catch
                    {
                        throw;
                    }
        
                }
        
                public void UpdateIndustry(int id, string name, string code, string definition)
                {
                    try
                    {
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_ID, SqlDbType.Int),
                            new SqlParameter(PARM_NAME, SqlDbType.NVarChar,250),
                            new SqlParameter(PARM_CODE, SqlDbType.NVarChar,50),
                            new SqlParameter(PARM_DEFINITION, SqlDbType.NText)};
                        parms[0].Value = id;
                        parms[1].Value = name;
                        parms[2].Value = code;
                        parms[3].Value = definition;
                        SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_UPDATE, parms);
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> GetIndustries()
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETALL);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null);
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> GetIndustryByLang(int lang)
                {
                    try
                    {
                        SqlParameter parm = new SqlParameter(PARM_LANG, SqlDbType.Int);
                        parm.Value = lang;
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBYLANG, parm);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
        
                }
        
                public List<IndustryUI> GetIndustryByClassAndLang(int classId, int langid)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_CLASSIFICATION, SqlDbType.Int),
                            new SqlParameter(PARM_LANG, SqlDbType.Int)};
                        parms[0].Value = classId;
                        parms[1].Value = langid;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBY_CLASSANDLANG, parms);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
        
                }
        
                public List<IndustryUI> GetTopLevelIndustryInClass(int classId)
                {
                    try
                    {
                        SqlParameter parm1 = new SqlParameter(PARM_CLASSIFICATION, SqlDbType.Int);
                        parm1.Value = classId;
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_TOPLEVELINCLASS, parm1);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public IndustryUI GetIndustryById(int id)
                {
                    try
                    {
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = id;
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBYID, parm1);
                        if (rdr.HasRows)
                        {
                            rdr.Read();
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null);
                            rdr.Close();
                            return item;
                        }
        
                        return null;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
        
                public int GetIndustryIdByCode(string code)
                {
                    int ID = 0;
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter param = new SqlParameter(PARM_CODE, SqlDbType.NVarChar, 20);
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETCHILDREN, param);
        
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt32(rdr["ID"]);
                        }
                        rdr.Close();
                        return ID;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> GetIndustryByName(string name)
                {
                    throw new NotImplementedException();
                }
        
                public List<IndustryUI> GetChildrenIndustry(int id)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter param = new SqlParameter(PARM_ID, SqlDbType.Int);
                        param.Value = id;
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETCHILDREN, param);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> SearchIndustry(string keyword)
                {
                    throw new NotImplementedException();
                }
                
                public List<IndustryUI> GetIndustryByLangAndLevel(int langId, int level)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_LEVEL, SqlDbType.Int)};
                        parms[0].Value = langId;
                        parms[1].Value = level;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBY_IDANDLEVEL, parms);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
                
                public List<IndustryUI> GetIndustryByLangAndCode(int langId, string code)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_CODE, SqlDbType.NVarChar)};
                        parms[0].Value = langId;
                        parms[1].Value = code;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBY_LANGANDCODE, parms);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> GetIndustryByLangAndClass(int langId, int classId)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_CLASSIFICATION, SqlDbType.Int)};
                        parms[0].Value = langId;
                        parms[1].Value = classId;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETBY_LANGANDCLASS, parms);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<IndustryUI> GetTopLevelByLangAndClass(int langId, int classId)
                {
                    try
                    {
                        List<IndustryUI> industryList = new List<IndustryUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_CLASSIFICATION, SqlDbType.Int)};
                        parms[0].Value = langId;
                        parms[1].Value = classId;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_TOPLEVEL_BYLANGANDCLASS, parms);
                        while (rdr.Read())
                        {
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            industryList.Add(item);
                        }
                        rdr.Close();
                        return industryList;
                    }
                    catch
                    {
                        throw;
                    }
                }
                public int GetMaxLevel()
                {
                    SqlDataReader rdr = null;
                    try
                    {
                        rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETMAXLEVEL);
                        rdr.Read();
                        return Convert.ToInt32(rdr["MAXLEVEL"]);
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        rdr.Close();
                    }
                }
                public IndustryUI GetParrentIndustry(int id)
                {
                    try
                    {
                        SqlParameter param = new SqlParameter(PARM_ID, SqlDbType.Int, 10);
                        SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INDUSTRY_GETCHILDREN, param);
                        if (rdr.HasRows)
                        {
                            rdr.Read();
                            IndustryUI item = new IndustryUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["CODE"].ToString(), Convert.ToInt32(rdr["CLASSIFICATION"]), rdr["CLASSIFICATIONTEXT"].ToString(), Convert.ToInt32(rdr["BELONGTO"]), rdr["BELONGTOTEXT"].ToString(), rdr["DEFINITION"].ToString(), rdr["COUNTRY"].ToString(), Convert.ToInt32(rdr["LANG"]), Convert.ToInt32(rdr["_LEVEL"]), null); ;
                            rdr.Close();
                            return item;
                        }
        
                        return null;
                    }
                    catch
                    {
                        throw;
                    }
        
                }
                #endregion*/
        
    }
}
